using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
using System.Data;
using System.Collections;
using System.Windows.Forms;
using System.ComponentModel;
using CocaCola_Inventory_System.Data_Access;
namespace CocaCola_Inventory_System
{
    class Insert
    {
         //String Connectionstring = "Driver={MySQL ODBC 3.51 Driver};Server=10.87.212.173;Port=3306;Database=inventorydb;User=root; Password=kym07;Option=3;";
        OdbcConnection con;
        BackgroundWorker worker;
        public Insert(OdbcConnection connect)
        {
            con = connect;

        }
        public bool item(string location, string condition, string tagging, string serial, string name, decimal acq, decimal accum, decimal book, DateTime pur, int refresh, DateTime redate, int type, int user, int ad, int depres, object[] names, object[] value, int accid, DateTime start, DateTime end, bool alert, int locid)
        {
            OdbcCommand ins = new OdbcCommand("CALL Items_Insert('" + location +
                                                           "','" + tagging +
                                                           "','" +  condition+
                                                           "','" + serial +
                                                           "','" +name +
                                                           "'," + acq +
                                                           "," + accum +
                                                           "," + book +
                                                           ",'" + pur.Year+"/"+pur.Month+"/"+pur.Day +
                                                           "','" + refresh +
                                                           "','" + redate.Year + "/" + redate.Month + "/" + redate.Day +
                                                           "'," + type +
                                                           //"," + user +
                                                           "," + ad +
                                                           "," + depres +
                                                            "," + locid +
                                                           ")");
            ins.Connection = con;
            OdbcTransaction trans = null;
            ins.Transaction = trans;
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();
                trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
                
               
                ins.Transaction = trans;



                int count = ins.ExecuteNonQuery();
               // OdbcCommand getid = new OdbcCommand("select max(id) from items", con, trans);
                ins.CommandText = "select max(id) from items";
                int id = (Convert.ToInt32(ins.ExecuteScalar()));
                //OdbcCommand det = new OdbcCommand();
                //det.Connection = con;
                //det.Transaction = trans;
                for (int i = 0; i < names.Length && i < value.Length; i++)
                {
                    ins.CommandText = "Call details_insert(" + names[i].ToString() + ",'" + value[i].ToString() + "'," + id + ");";
                    ins.ExecuteNonQuery();
                }
               // OdbcCommand us = new OdbcCommand("insert into ownership (item_id,user_id,start_date) values ("+id+","+user+",NOW() )",con);
                ins.CommandText = "insert into ownership (item_id,user_id,start_date,end_date) values (" + id + "," + user + ",'" + start.Year + "/" + start.Month + "/" + start.Day + "','" + end.Year + "/" + end.Month + "/" + end.Day + "')";
                //ins.Transaction = trans;
                ins.ExecuteNonQuery();

                ins.CommandText = "select email from users where id =" + user;
                string email = ins.ExecuteScalar().ToString();
                if(email!=""&&alert)
                    EMAIL.sendemail(email, "Start of Assignment", " Assignment of item " + name + " will start in " + start.ToLongDateString() + "\n Please Contact IT department for more Information");

                ins.CommandText = "insert into log (account_id,Action,Table_Name,record_id,stamp) values (" + accid + ",'insert','items'," + id + ",NOW())";
                ins.ExecuteNonQuery();
                trans.Commit();


            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
                trans.Rollback();
                if(! (con.State ==ConnectionState.Closed))
               // con.Close();
                return false;

            }
           
            //if (!(con.State == ConnectionState.Closed))
           // con.Close();
            return true;
        }
        public bool type(string name, string Desc,int accid)
        {
            OdbcCommand ins = new OdbcCommand("select count(*) from types where name like '"+name+"'");
            int count=0;
            ins.Connection = con;
            OdbcTransaction trans = null;
            ins.Transaction = trans;
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();

                trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
                ins.Transaction = trans;
                int pre = Convert.ToInt32(ins.ExecuteScalar().ToString());
                if (pre > 0)
                {
                    trans.Rollback();
                    MessageBox.Show("Item Type Already Exists");
                    return false;
                }

                ins.CommandText = "CALL types_insert('" + name + "','" + Desc + "')";
                count = ins.ExecuteNonQuery();

                ins.CommandText = "insert into log (account_id,Action,Table_Name,stamp) values (" + accid + ",'insert','type' ,NOW())";
                ins.ExecuteNonQuery();
                trans.Commit();
                
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
                //if (!(con.State == ConnectionState.Closed))
                   // con.Close();
                trans.Rollback();
                return false;
            }
            //if (!(con.State == ConnectionState.Closed))
               // con.Close();
            return (count==1);
        }

        public bool detail_type(string name, string Desc,int accid)
        {
            OdbcCommand ins = new OdbcCommand("select count(*) from detail_types where name like '"+name+"'");

                    
            int count = 0;
            //odbc
            ins.Connection = con;
            OdbcTransaction trans = null;
            ins.Transaction = trans;
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();
                trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
                ins.Transaction = trans;
                int pre = Convert.ToInt32(ins.ExecuteScalar().ToString());
                if (pre > 0)
                {
                    trans.Rollback();
                    MessageBox.Show("Detail Type Already Exist");
                    return false ;
                }
                ins.CommandText = "CALL Detail_Types_Insert('" + name + "','" + Desc + "')";                                    
                count = ins.ExecuteNonQuery();
                

                ins.CommandText = "insert into log (account_id,Action,Table_Name,stamp) values (" + accid + ",'insert','Detail Type',NOW())";

                ins.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception err)
            {

                //if (!(con.State == ConnectionState.Closed))
                   // con.Close();
                trans.Rollback();
                return false;
            }
            //if (!(con.State == ConnectionState.Closed))
               // con.Close();
            return (count == 1);
        }

        public bool update(int id, string location, string condition, string tagging, string serial, string name, decimal acq, decimal accum, decimal book, DateTime pur, int refresh, DateTime redate, int type, int user, int ad, int depres, int account, object[] names, object[] value, object[] deleted, DateTime endp, DateTime startn, DateTime endn, bool alert, int tempuser, int locid)
        {
            
            if (con.State != ConnectionState.Open)
                con.Open();
            OdbcTransaction trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
            OdbcCommand com = new OdbcCommand();
            com.Connection = con;
            com.Transaction = trans;
            
            try
            {
                for (int i = 0; i < deleted.Length; i++)
                {
                    com.CommandText = "delete from details where id = " + deleted[i].ToString();
                    com.ExecuteNonQuery();
                }

                for (int i = 0; i < names.Length && i < value.Length; i++)
                {
                    com.CommandText = "Call Details_insert(" + names[i].ToString() + ",'" + value[i].ToString() + "'," + id.ToString() + ");";
                    com.ExecuteNonQuery();
                }
                object o = null ;
                object y = null;
                if (user >= 0)
                {
                    com.CommandText = "call getmailofowner(" + id + ")";
                    o = com.ExecuteScalar();
                    com.CommandText = "CALL update_ownership(" + id + "," + user + ",'" + endp.Year + "/" + endp.Month + "/" + endp.Day + "','"+ startn.Year + "/" + startn.Month + "/" + startn.Day + "','"+ endn.Year + "/" + endn.Month + "/" + endn.Day + "',"+tempuser+")";
                    com.ExecuteNonQuery();
                    com.CommandText = "call getmailofowner(" + id + ")";
                    y = com.ExecuteScalar();

                }

                com.CommandText = "select Val from category where id=" + refresh;
                int rc;
                try
                {
                    rc = Convert.ToInt32(com.ExecuteScalar().ToString());
                }
                catch
                {
                    trans.Rollback();
                    return false;
                }
                com.CommandText = "select val from depresionation where id= " + depres;
                
                int dep;
                try
                {
                    string s = com.ExecuteScalar().ToString();
                    dep = Convert.ToInt32(s);
                }
                catch
                {
                    trans.Rollback();
                    return false;
                }

                redate = pur.AddYears(rc);
                accum = (acq / dep) * ((DateTime.Now.Month - pur.Month) + ((DateTime.Now.Year - pur.Year) * 12));
                book = acq - accum;

                if (book < 0)
                    book = 0;

              



                com.CommandText = "update items set ";
                string query = "";
                if(location!="")
                    query+= ",location = '" + location + "'";
                if(condition!="")
                query+=", cond = '" + condition + "'";
                if(tagging!="")
                    query += ", tagging = '" + tagging + "'";
                if(serial!="")
                    query += ",serial_no = '" + serial + "'";
                if(name!="")
                    query += ", name = '" + name + "'";
                if (acq > -1)
                {
                    query += ",acq_value = " + acq;

                    query += ", accum_value = " + accum;

                    query += ",book_value = " + book;
                }
                if(pur!=null)
                    query += ",Purchase_Date = '" + pur.Year + "/" + pur.Month + "/" + pur.Day + "'";
                if (refresh > -1)
                    query += ", Refresh_Category = " + refresh + "";
                if(redate!=null)
                    query += ",Refresh_Date = '" + redate.Year + "/" + redate.Month + "/" + redate.Day + "'";
                if (type >= 0)
                    query += ",type_id  = " + type;
               // if (user >= 0)
                   //com.CommandText+= ", user_id = " + user;
                if(ad>=0)
                    query += ",ad_id = " + ad;
                ;
                if (depres > -1)
                    query += ",Depres = "+depres;
                if (locid >= 0)
                    query += ",Location_id =" + locid;

                com.CommandText += query.Substring(1);
                com.CommandText += " where id = " + id;
                com.ExecuteNonQuery();

                com.CommandText = "insert into log (account_id,action,Table_name,record_id,stamp) values ("+account+",'Update','Items',"+id+",NOW())";
                com.ExecuteNonQuery();

                if (user > 0)
                {

                }
                if (o != null && o != DBNull.Value)
                {
                    string to = o.ToString();
                    if(to!=""&&alert)
                        EMAIL.sendemail(to, "End of Assignment", "End of Assignment of item " + name + " is due in " + endp.ToLongDateString() + "\n Please Contact IT department for more Information");
                }
                if (y != null && y != DBNull.Value)
                {
                    string to = y.ToString();
                    if (to != ""&&alert)
                        EMAIL.sendemail(to, "Start of Assignment", " Assignment of item " + name + " will start in "+startn.ToLongDateString()+"\n Please Contact IT department for more Information");
                }

                trans.Commit();
            }
            catch (Exception x)
            {
                MessageBox.Show(x.Message);
                trans.Rollback();
                return false;
            }
            finally
            {
                //if (con.State != ConnectionState.Closed)
                   // con.Close();
                
            }
            return true;


               
        }
        public bool deleteRecord(int id,int accid)
         {
             OdbcTransaction trans = null;
             try
             {
                 if (con.State != ConnectionState.Open)
                     con.Open();
                 trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
                 OdbcCommand com = new OdbcCommand("delete from details  where item_id =" + id, con);
                 com.Transaction = trans;
                 com.ExecuteNonQuery();
                 com.CommandText = "delete from ownership where item_id = " + id;
                 com.ExecuteNonQuery();
                 com.CommandText = "delete from items where id = " + id;

                 com.ExecuteNonQuery();
                 com.CommandText = "insert into log (account_id,action,Table_name,record_id,stamp) values (" + accid + ",'Delete','Items'," + id + ",NOW())";
                 com.ExecuteNonQuery();
                 trans.Commit();
                 //if (con.State != ConnectionState.Closed)
                    // con.Close();
                 return true;
             }
             catch (Exception x)
             {
                 MessageBox.Show(x.Message);
                 trans.Rollback();
                 return false;
             }
             
        }
        public bool condition(string name, string desc,int accid)
        {
            OdbcTransaction trans = null;
            OdbcCommand ins = new OdbcCommand("select count(*) from conditions where name like '"+name+"'");
            int count = 0;
            ins.Connection = con;
            if (con.State != ConnectionState.Open)
                con.Open();

            try
            {
                
                trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
                ins.Transaction = trans;
                int pre = Convert.ToInt32(ins.ExecuteScalar().ToString());
                if (pre > 0)
                {
                    trans.Rollback();
                    MessageBox.Show("Condition Already Exists");
                    return false;
                }
                ins.CommandText = "insert into conditions(name,Descripition) values('" + name + "','" + desc + "')";
                count = ins.ExecuteNonQuery();
                ins.CommandText = "insert into log (account_id,Action,Table_Name,stamp) values (" + accid + ",'insert','Condition',NOW())";
                ins.ExecuteNonQuery();
                trans.Commit();
            }
            catch(Exception ex)
            {
                trans.Rollback();
                MessageBox.Show(ex.Message);
                return false;
            }

            //if (!(con.State == ConnectionState.Closed))
               // con.Close();
            return true;

        }

        public bool category(string name,int value, string desc,int accid)
        {
            OdbcTransaction trans = null;
            OdbcCommand ins = new OdbcCommand("select count(*) from category where name like '"+name+"'");
            int count = 0;
            ins.Connection = con;
            if (con.State != ConnectionState.Open)
                con.Open();
            try
            {
                trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
                ins.Transaction = trans;
                int pre = Convert.ToInt32(ins.ExecuteScalar().ToString());
                if(pre>0)
                {
                    trans.Rollback();
                    MessageBox.Show("Category Already Exists");
                    return false;
                }
                ins.CommandText = "insert into category(name,val,Description) values('" + name + "'," + value + ",'" + desc + "')";
                count = ins.ExecuteNonQuery();
                ins.CommandText = "insert into log (account_id,Action,Table_Name,stamp) values (" + accid + ",'insert','Condition',NOW())";
                ins.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                return false;
            }

            //if (!(con.State == ConnectionState.Closed))
               // con.Close();
            return true;

        }

        public bool depres(string name, int value, string desc,int accid)
        {
            OdbcTransaction trans = null;
            OdbcCommand ins = new OdbcCommand("select count(*) from depresionation where name like '"+name+"'");
            int count = 0;
            ins.Connection = con;
            if (con.State != ConnectionState.Open)
                con.Open();
            try
            {
                trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
                ins.Transaction = trans;
                int pre = Convert.ToInt32(ins.ExecuteScalar().ToString());
                if(pre>0)
                {
                    trans.Rollback();
                    MessageBox.Show("Depreciation Category Already Exits");
                    return false;
                }
                ins.CommandText = "insert into depresionation(name,val,Description) values('" + name + "'," + value + ",'" + desc + "')";
                count = ins.ExecuteNonQuery();
                ins.CommandText = "insert into log (account_id,Action,Table_Name,stamp) values (" + accid + ",'insert','depresiation',NOW())";
                ins.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                return false;
            }

            //if (!(con.State == ConnectionState.Closed))
               // con.Close();
            return true;

        }

        public bool deleteaccount(int id,int idres)
        {
            OdbcCommand ins = new OdbcCommand("update accounts set deleted = 1 where id = "+id,con);

            
            
            if (con.State != ConnectionState.Open)
                con.Open();
            OdbcTransaction trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
            ins.Transaction = trans;
            int count = 0;
            ins.Connection = con;
            if (con.State != ConnectionState.Open)
                con.Open();
            try
            {
                ins.ExecuteNonQuery();
                ins.CommandText = "insert into log (account_id,action,stamp) values ('" + idres + "','Delete User',NOW())";
                ins.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                return false;
            }

            
            

            //if (!(con.State == ConnectionState.Closed))
               // con.Close();
            return true;
        }

        public bool addaccount(string name, string password, int security,int idres)
        {
            OdbcCommand ins = new OdbcCommand("insert into accounts (name,pass,securitylevel) values ('"+name+"','"+password+"',"+security+")", con);

            if (con.State != ConnectionState.Open)
                con.Open();
            OdbcTransaction trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
            ins.Transaction = trans;
            int count = 0;
            ins.Connection = con;
            if (con.State != ConnectionState.Open)
                con.Open();
            try
            {
                count = ins.ExecuteNonQuery();
                ins.CommandText = "insert into log (account_id,action,stamp) values ('" + idres + "','Create User',NOW())";
                ins.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                return false;
            }




            //if (!(con.State == ConnectionState.Closed))
               // con.Close();
            return (count==1);
        }

        public bool updateaccount(string name, string pass, int sec,int id, int idres)
        {
            OdbcCommand ins = new OdbcCommand("update accounts set name = '" + name + "',SecurityLevel = " + sec, con);
            if (pass != "")
                ins.CommandText += ",pass = '" + pass+"'";
            ins.CommandText += "  where id = " + id;
            if (con.State != ConnectionState.Open)
                con.Open();
            OdbcTransaction trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
            ins.Transaction = trans;
            int count = 0;
            ins.Connection = con;
            if (con.State != ConnectionState.Open)
                con.Open();
            try
            {

                count = ins.ExecuteNonQuery();
                ins.CommandText = "insert into log (account_id,action,table_name,record_id,stamp) values ('" + idres + "','updateuser','accounts'," + id + ",NOW())";
                ins.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                return false;
            }




            //if (!(con.State == ConnectionState.Closed))
               // con.Close();
            return (count == 1);
        }

        public bool deletetype(int id, int accid)
        {
            if (con.State != ConnectionState.Open)
                con.Open();


            OdbcCommand com = new OdbcCommand("delete from types where id = " + id, con);
            OdbcTransaction trans = con.BeginTransaction();
            com.Transaction = trans;
            try
            {
                com.ExecuteNonQuery();
                com.CommandText = "insert into log (account_id,action,Table_name,record_id,stamp) values (" + accid + ",'Delete','Types'," + id + ",NOW())";
                com.ExecuteNonQuery();
                trans.Commit();
                return true;
            }
            catch
            {
                trans.Rollback();
                return false;
            }

        }

        public bool deletedetail_type(int id, int accid,int resid)
        {
            if (con.State != ConnectionState.Open)
                con.Open();


            OdbcCommand com = new OdbcCommand("update details set detail_type_id = "+resid+" where detail_type_id ="+id, con);
            OdbcTransaction trans = con.BeginTransaction();
            com.Transaction = trans;
            try
            {
                com.ExecuteNonQuery();
                com.CommandText = "delete from detail_types where id = " + id;
                com.ExecuteNonQuery();
                com.CommandText = "insert into log (account_id,action,Table_name,record_id,stamp) values (" + accid + ",'Delete','detail_types'," + id + ",NOW())";
                com.ExecuteNonQuery();
                trans.Commit();
                return true;
            }
            catch
            {
                trans.Rollback();
                return false;
            }

        }

        public bool endownership(int id, DateTime end, int accid,int tempuser)
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            
            OdbcCommand com = new OdbcCommand();
            com.Connection = con;
            com.CommandText = "Call getmailofowner(" + id + ")";

            OdbcTransaction trans = con.BeginTransaction();
            com.Transaction = trans;
            object o;
            try
            {
                o = com.ExecuteScalar();                
                com.CommandText = "select name from items where id = "+id;
                string name = com.ExecuteScalar().ToString();
                com.CommandText = "CAll Update_OwnerShip(" + id + ","+tempuser+", '" + end.Year + "/" + end.Month + "/" + end.Day + "','" + end.Year + "/" + end.Month + "/" + end.Day + "','" + DateTime.MaxValue.Year + "/" + DateTime.MaxValue.Month + "/" + DateTime.MaxValue.Day + "',"+tempuser+")";
                com.ExecuteNonQuery();
                com.CommandText = "insert into log (account_id,action,Table_name,record_id,stamp) values (" + accid + ",'End OwnerShip','ownership'," + id + ",NOW())";
                com.ExecuteNonQuery();
                trans.Commit();
                if(o!= DBNull.Value)
                {
                    string to = o.ToString();
                    if(to!="")
                        EMAIL.sendemail(to, "End of Assignment", "End of Assignment of item " + name + " is due in " + DateTime.Now.ToLongDateString() + "\n Please Contact IT department for more Information");
                }
                
                return true;
            }
            catch(Exception ex)
            {
                trans.Rollback();
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public bool deletelog(int accid)
        {
            if (con.State != ConnectionState.Open)
                con.Open();
            OdbcTransaction trans = con.BeginTransaction();
            
            OdbcCommand com = new OdbcCommand("delete from Log", con,trans);
            try
            {

                com.ExecuteNonQuery();
                com.CommandText = "insert into log (account_id,action,Table_name,stamp) values (" + accid + ",'DELETE LOG','log',NOW())";
                com.ExecuteNonQuery();
                trans.Commit();
                return true;
            }
            catch
            {
                trans.Rollback();
                return false;
            }

            
        }

        public bool insertbu(string name, string desc, int accid)
        {
            OdbcTransaction trans = null;
            OdbcCommand ins = new OdbcCommand("select count(*) from bu where name like '" + name + "'");
            int count = 0;
            ins.Connection = con;
            if (con.State != ConnectionState.Open)
                con.Open();
            try
            {
                trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
                ins.Transaction = trans;
                int pre = Convert.ToInt32(ins.ExecuteScalar().ToString());
                if (pre > 0)
                {
                    trans.Rollback();
                    MessageBox.Show("BU Already Exists");
                    return false;
                }
                ins.CommandText = "insert into bu(name,Description) values('" + name + "','" + desc + "')";
                count = ins.ExecuteNonQuery();
                ins.CommandText = "insert into log (account_id,Action,Table_Name,stamp) values (" + accid + ",'insert','bu',NOW())";
                ins.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                return false;
            }

            //if (!(con.State == ConnectionState.Closed))
            // con.Close();
            return true;
        }

        public bool insertcountry(int buid, string name, int accid)
        {
            OdbcTransaction trans = null;
            OdbcCommand ins = new OdbcCommand("select count(*) from countries where name like '" + name + "'");
            int count = 0;
            ins.Connection = con;
            if (con.State != ConnectionState.Open)
                con.Open();
            try
            {
                trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
                ins.Transaction = trans;
                int pre = Convert.ToInt32(ins.ExecuteScalar().ToString());
                if (pre > 0)
                {
                    trans.Rollback();
                    MessageBox.Show("Country Already Exists");
                    return false;
                }
                ins.CommandText = "insert into countries(name,bu_id) values('" + name + "',"+buid+")";
                count = ins.ExecuteNonQuery();
                ins.CommandText = "insert into log (account_id,Action,Table_Name,stamp) values (" + accid + ",'insert','countries',NOW())";
                ins.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                return false;
            }

            //if (!(con.State == ConnectionState.Closed))
            // con.Close();
            return true;
        }

        public bool insertlocation(int couid, string name, string address, string desc, int accid)
        {
            OdbcTransaction trans = null;
            OdbcCommand ins = new OdbcCommand("select count(*) from locations where name like '" + name + "'");
            int count = 0;
            ins.Connection = con;
            if (con.State != ConnectionState.Open)
                con.Open();
            try
            {
                trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
                ins.Transaction = trans;
                int pre = Convert.ToInt32(ins.ExecuteScalar().ToString());
                if (pre > 0)
                {
                    trans.Rollback();
                    MessageBox.Show("Location Already Exists");
                    return false;
                }
                ins.CommandText = "insert into locations(name,address,Description,country_id) values('" + name + "','" + address + "','" + desc + "',"+couid+")";
                count = ins.ExecuteNonQuery();
                ins.CommandText = "insert into log (account_id,Action,Table_Name,stamp) values (" + accid + ",'insert','locations',NOW())";
                ins.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                return false;
            }

            //if (!(con.State == ConnectionState.Closed))
            // con.Close();
            return true;
        }

        public bool updatelocation(int id, string name, string add, string desc, int couid, int accid)
        {
            OdbcTransaction trans = null;
            OdbcCommand ins = new OdbcCommand();
            int count = 0;
            ins.Connection = con;
            if (con.State != ConnectionState.Open)
                con.Open();
            try
            {
                trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
                ins.Transaction = trans;
               
                ins.CommandText = "update locations set name = '"+name+"',address = '"+add+"',description = '"+desc+"',country_id = "+couid+" where id = "+id+"";
                count = ins.ExecuteNonQuery();
                ins.CommandText = "insert into log (account_id,Action,Table_Name,record_id,stamp) values (" + accid + ",'update','locations',"+id+",NOW())";
                ins.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                return false;
            }

            //if (!(con.State == ConnectionState.Closed))
            // con.Close();
            return true;
        }

        public bool updatecountry(int id, string name, int buid, int accid)
        {
            OdbcTransaction trans = null;
            OdbcCommand ins = new OdbcCommand();
            int count = 0;
            ins.Connection = con;
            if (con.State != ConnectionState.Open)
                con.Open();
            try
            {
                trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
                ins.Transaction = trans;

                ins.CommandText = "update countries set name = '" + name + "',bu_id = " + buid + " where id = " + id + "";
                count = ins.ExecuteNonQuery();
                ins.CommandText = "insert into log (account_id,Action,Table_Name,record_id,stamp) values (" + accid + ",'update','countries'," + id + ",NOW())";
                ins.ExecuteNonQuery();
                trans.Commit();
            }
            catch(Exception ex)
            {
                trans.Rollback();
                return false;
            }

            //if (!(con.State == ConnectionState.Closed))
            // con.Close();
            return true;
        }

        public bool updatebu(int id, string name,string desc, int accid)
        {
            OdbcTransaction trans = null;
            OdbcCommand ins = new OdbcCommand();
            int count = 0;
            ins.Connection = con;
            if (con.State != ConnectionState.Open)
                con.Open();
            try
            {
                trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
                ins.Transaction = trans;

                ins.CommandText = "update bu set name = '" + name + "',description ='"+desc+"' where id = " + id + "";
                count = ins.ExecuteNonQuery();
                ins.CommandText = "insert into log (account_id,Action,Table_Name,record_id,stamp) values (" + accid + ",'update','bu'," + id + ",NOW())";
                ins.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                return false;
            }

            //if (!(con.State == ConnectionState.Closed))
            // con.Close();
            return true;
        }

        public bool deletelocation(int id , int accid)
        {
                if (con.State != ConnectionState.Open)
                con.Open();


            OdbcCommand com = new OdbcCommand("delete from locations where id = " + id, con);
            OdbcTransaction trans = con.BeginTransaction();
            com.Transaction = trans;
            try
            {
                com.ExecuteNonQuery();
                com.CommandText = "insert into log (account_id,action,Table_name,record_id,stamp) values (" + accid + ",'Delete','locations'," + id + ",NOW())";
                com.ExecuteNonQuery();
                trans.Commit();
                return true;
            }
            catch
            {
                trans.Rollback();
                return false;
            }
        }

        public bool deletecountry(int id, int accid)
        {
            if (con.State != ConnectionState.Open)
                con.Open();


            OdbcCommand com = new OdbcCommand("delete from countries where id = " + id, con);
            OdbcTransaction trans = con.BeginTransaction();
            com.Transaction = trans;
            try
            {
                com.ExecuteNonQuery();
                com.CommandText = "insert into log (account_id,action,Table_name,record_id,stamp) values (" + accid + ",'Delete','countries'," + id + ",NOW())";
                com.ExecuteNonQuery();
                trans.Commit();
                return true;
            }
            catch
            {
                trans.Rollback();
                return false;
            }
        }

        public bool deletebu(int id, int accid)
        {
            if (con.State != ConnectionState.Open)
                con.Open();


            OdbcCommand com = new OdbcCommand("delete from bu where id = " + id, con);
            OdbcTransaction trans = con.BeginTransaction();
            com.Transaction = trans;
            try
            {
                com.ExecuteNonQuery();
                com.CommandText = "insert into log (account_id,action,Table_name,record_id,stamp) values (" + accid + ",'Delete','bu'," + id + ",NOW())";
                com.ExecuteNonQuery();
                trans.Commit();
                return true;
            }
            catch
            {
                trans.Rollback();
                return false;
            }
        }

        public bool updatedetail_type(int id, string name, string desc, int accid)
        {
            if (con.State != ConnectionState.Open)
                con.Open();


            OdbcCommand com = new OdbcCommand("update detail_types set name = '"+name+"', description = '"+desc+"' where id = "+id, con);
            OdbcTransaction trans = con.BeginTransaction();
            com.Transaction = trans;
            try
            {
                com.ExecuteNonQuery();
                com.CommandText = "insert into log (account_id,action,Table_name,record_id,stamp) values (" + accid + ",'update','detail_types'," + id + ",NOW())";
                com.ExecuteNonQuery();
                trans.Commit();
                return true;
            }
            catch
            {
                trans.Rollback();
                return false;
            }
        }
        
        
    }
}
